﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Marta.buchalska.wesele.DataModel;
using System.Data;
using System.Web.Security;

namespace Marta.buchalska.wesele.Model
{
    public class DataBaseGateway
    {
        private string _connectionString;

        public PhotoManager Gallery { get { return new Model.PhotoManager(); } set { } }

        public DataBaseGateway()
        {
            _connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["gardini_weedingConnectionString"].ConnectionString;
        }

        public User ValidateUser(string login, string password)
        {
            password = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "SHA1");
            string command = string.Format(@"SELECT        login
                                            FROM            [User]
                                            WHERE        (login LIKE '{0}') AND (password LIKE '{1}')", login, password);
            var user = ExecuteComemmand(command);
            if (user.Tables[0].Rows.Count > 0)
                return new User() { IsAuthenticated = true };
            else
                return null;
        }

        public DataSet ExecuteComemmand(string commandTxt)
        {
            var resoult = new DataSet();
            System.Data.SqlClient.SqlDataAdapter adr = new System.Data.SqlClient.SqlDataAdapter(commandTxt, _connectionString);
            adr.Fill(resoult);
            return resoult;
        }

        /// <summary>
        /// pobieranie treści stron
        /// </summary>
        /// <param name="pageName">nazwa strony do pobrania</param>
        internal DataSet GetPage(string pageName)
        {
            string command = string.Format("Select body from Pages where name = '{0}'", pageName);
            return ExecuteComemmand(command);
        }

        /// <summary>
        /// aktualizacja tekstu na stronie 
        /// </summary>
        /// <param name="pageName">nazwa strony jaka ma być aktualizownan</param>
        /// <param name="pageBody">treść strony</param>
        internal void UpdatePage(string pageName, string pageBody)
        {
            string command = string.Format(@"UPDATE [gardini_weeding].[gardini_gardini].[Pages] SET [body] = '{1}' WHERE name = '{0}'", pageName, pageBody);
            ExecuteComemmandUpdate(command);
        }

        private void ExecuteComemmandUpdate(string commandText)
        {
            using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this._connectionString))
            {
                var command = con.CreateCommand();
                command.CommandText = commandText;
                command.CommandType = CommandType.Text;
                con.Open();
                command.ExecuteNonQuery();
                con.Close();
            }
        }

        /// <summary>
        /// Pobieramy liste kategorii galerii zdjęć
        /// </summary>
        internal DataSet GetPhotoCategory()
        {
            string command = string.Format(@"SELECT [photo_category_name],[photo_category_id] ,[photo_count] FROM [gardini_weeding].[gardini_gardini].[View_Photo_category_photo_count]");
            return ExecuteComemmand(command);
        }
    }
}